package com.example.demo2.mapper;

import com.example.demo2.dto.*;
import com.example.demo2.entity.*;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface StudyMapper {

    @Select("select * from study where uID=#{uID}")
    public List<Study> findStudyByUID(@Param("uID") Integer uID);

    @Select("select * from study where uID=#{uID} and pjId=#{pjId}")
    public Study findStudyByPjId(@Param("uID") Integer uID,@Param("pjId") Integer pjId);

    @Update("update study set PaperStatus=true where sId=#{sId}")
    public int updatePaperStatus(@Param("sId") Integer sId);

    @Update("update study set PaperUrl=#{pdfUrl} where sId=#{sId}")
    public int updatePaperUrl(@Param("sId") Integer sId,@Param("pdfUrl") String pdfUrl);

    @Select("select * from study where uID=#{uID} and study.pjId=#{query}")
    public Study searchStudyByPjId(@Param("uID") Integer uID, @Param("query") Integer query);

    @Insert("insert into study(sId, uID, pjId, sStatus, PaperStatus,startTime, pass) values (NULL,#{uID},#{pjId},0,0,#{startTime},0)")
    public int insertStudy(Study study);

    @Update("update study set sStatus=#{sStatus} where sId=#{sId}")
    public int updateStatus(@Param("sId") Integer sId,@Param("sStatus") double sStatus);

    @Select("select * from study where startTime like #{query} and uID=#{uID}")
    public List<Study> searchStudyByStartTime(@Param("query") String query,@Param("uID") Integer uID);

    @Update("update study set PaperUrl = #{paperUrl} where sId=#{sId}")
    public int uploadPaperUrl(@Param("paperUrl") String paperUrl,@Param("sId") Integer sId);

    @Update("update study set VideoUrl = #{videoUrl} where sId=#{sId}")
    public int uploadVideoUrl(@Param("videoUrl") String videoUrl,@Param("sId") Integer sId);

    @Select("select study.PaperUrl from study where sId = 1")
    public String test();


    // 根据项目 ID 获取培训记录
    @Select("SELECT p.peId, u.uName, pc.pmcTime, p.peType " +
            "FROM permissionconnection pc " +
            "JOIN users u ON pc.uID = u.uID " +
            "JOIN permission p ON pc.pmcId = p.peId " +
            "WHERE pc.pjId = #{pjId} AND p.peType = 1")
    List<TrainingRecordDTO> getTrainingRecordsByProjectId(@Param("pjId") int projectId);



    // 根据项目 ID 获取培训视频
    @Select("SELECT pgId, pgName, pgUrl, updateTime, pgType FROM program WHERE pjId = #{pjId} AND pgType = '培训视频'")
    List<ProgramDTO> getTrainingVideosByProjectId(@Param("pjId") int projectId);

    // 根据项目 ID 获取考核试卷
    @Select("SELECT pgId, pgName, pgUrl, updateTime, pgType FROM program WHERE pjId = #{pjId} AND pgType = '考核试卷'")
    List<ProgramDTO> getAssessmentPapersByProjectId(@Param("pjId") int projectId);



    @Select("SELECT * FROM program WHERE pgId = #{pgId}")
    ProgramDTO getProgramById(@Param("pgId") int programId);



    // 上传培训视频
    @Insert("INSERT INTO program (pjId, pgType, pgName, pgUrl, updateTime) VALUES (#{pjId}, '培训视频', #{pgName}, #{pgUrl}, NOW())")
    void uploadTrainingVideo(@Param("pjId") int projectId, @Param("pgName") String pgName, @Param("pgUrl") String pgUrl);

    // 上传考核试卷
    @Insert("INSERT INTO program (pjId, pgType, pgName, pgUrl, updateTime) VALUES (#{pjId}, '考核试卷', #{pgName}, #{pgUrl}, NOW())")
    void uploadExam(@Param("pjId") int projectId, @Param("pgName") String pgName, @Param("pgUrl") String pgUrl);

    // 删除培训视频
    @Delete("DELETE FROM program WHERE pgId = #{pgId} AND pgType = '培训视频'")
    void deleteTrainingVideo(@Param("pgId") int programId);

    // 删除考核试卷
    @Delete("DELETE FROM program WHERE pgId = #{pgId} AND pgType = '考核试卷'")
    void deleteExam(@Param("pgId") int programId);

    //考核授权列表
    @Select("SELECT s.sId, u.uID, u.uName, s.startTime, s.sRead, s.pass FROM study s JOIN users u ON s.uId = u.uID WHERE s.pjId = #{projectId}")
    List<StudyRecordDTO> getStudyRecordsByProjectId(@Param("projectId") int projectId);

    // 根据学习表ID获取PaperUrl和VideoUrl
    @Select("SELECT sId, PaperUrl, VideoUrl FROM study WHERE sId = #{sId}")
    StudyResourcesDTO getStudyResourcesById(@Param("sId") int studyId);


    @Select("SELECT pc.pmcUrl FROM study s " +
            "JOIN permissionconnection pc ON s.pmID = pc.pmcId " +
            "WHERE s.sId = #{sId}")
    String getCertificateUrlByStudyId(@Param("sId") int sId);

    //@Select("SELECT * FROM study WHERE s_id = #{sId}")
    //StudyInfoDTO getStudyInfoById(@Param("sId") int sId);

    //@Update("UPDATE study SET certificate_url = #{certificateUrl} WHERE s_id = #{sId}")
    //void updateCertificateUrl(@Param("sId") int sId, @Param("certificateUrl") String certificateUrl);

    @Insert("INSERT INTO permission (peUrl, peType) VALUES (#{peUrl}, #{peType})")
    @Options(useGeneratedKeys = true, keyProperty = "peid")
    void insertPermission(Permission permission);

    @Insert("INSERT INTO permissionconnection (pmcId, uID, pjId, pmcUrl, pmcType, pmcTime) " +
            "VALUES (#{pmcid}, #{uid}, #{pjid}, #{pmUrl}, #{pmType}, NOW())")
    void insertPermissionConnect(PermissionConnectionDTO permissionConnection);


    @Update("UPDATE study SET pmID = #{peid} WHERE sId = #{sId}")
    void updateStudyPmid(@Param("sId") int sId, @Param("peid") int peid);

    @Update("UPDATE study SET pass = #{pass} WHERE sId = #{sId}")
    void updateStudyPass(@Param("sId") int sId, @Param("pass") int pass);
    @Update("UPDATE study SET sRead = #{sRead} WHERE sId = #{sId}")
    void updateStudysRead(@Param("sId") int sId, @Param("sRead") String sRead);
    @Select("SELECT pmcTime FROM permissionconnection WHERE pmcId = #{pmcId}")
    String getsReadpmcTime(@Param("pmcId") int pmcId);
    @Update("UPDATE study SET PaperUrl = #{inputFilePath} WHERE sId = #{sId}")
    void updateCertificateUrl(int sId, String inputFilePath);

    @Select("SELECT " +
            "u.uName, " +
            "u.uID," +
            "s.sRead, " +
            "s.pmID, " +
            "st.staName, " +
            "st.staGb, " +
            "s.pjId," +
            "p.pjName " +
            "FROM study s " +
            "JOIN users u ON s.uId = u.uID " +
            "JOIN project p ON s.pjId = p.pjId " +
            "JOIN standard st ON p.staId = st.staId " +
            "WHERE s.sId = #{sId}")
    StudyInfoDTO getStudyInfoById(@Param("sId") int sId);

    // 更新 Permission 表的 peurl
    @Update("UPDATE permission SET peUrl = #{peUrl} WHERE permission.peId = #{peid}")
    void updatePermissionUrl(@Param("peid") int peid, @Param("peUrl") String peUrl);

    // 更新 PermissionConnect 表的 pmurl
    @Update("UPDATE permissionconnection SET pmcUrl = #{pmUrl} WHERE pmcId = #{peid}")
    void updatePermissionConnectUrl(@Param("peid") int peid, @Param("pmUrl") String pmUrl);

    @Update("update study set sTime = #{sTime} where sId=#{sId}")
    public boolean updateSTime(@Param("sId") Integer sId,@Param("sTime") String sTime);



}
